package com.six.work.mapper;

import com.six.work.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface CompanyMapper {
    @Select("SELECT * FROM company")
    List<Company> findAllCompany();

    //前端页面Aworkcompany连数据库
    @Select("SELECT * FROM company where company_state='审核通过'")
    List<Company> findAllCompany1();

    @Delete("DELETE from company where company_id=#{company_id}")
    Integer DelCompanyByID(Integer company_id);

    @Select("select *,state.state from company join state on company.company_state=state.state where company.company_name like '%${companyName}%' and state.state_id=#{state_id} order by company.create_date desc;")
    List<CompanyView> finComByStateAndcomName(@Param("companyName")String companyName, @Param("state_id")Integer state_id);

    @Select("select *,state.state from  company join state on company.company_state=state.state where company.company_name like '%${companyName}%' order by company.create_date desc;")
    List<CompanyView> findCompanyByName(String companyName);

    @Select("SELECT * from company where company_name like '%${comName}%' order by company_id desc")
    List<Company> findCompanyManageByName(String comName);

    @Update("UPDATE company SET `company_state` = '审核未过' \n" +
            "WHERE (company_id = #{company_id})")
    String updateComState(Integer company_id);

    @Update("UPDATE company SET `company_state` = '审核通过' \n" +
            "WHERE (company_id = #{company_id})")
    String updateComState1(Integer company_id);

    @Select("select * from company where company_number=#{company_number}")
    Company UserLogin(String company_number);

//    @Insert("insert into company (company_name,company_number,company_pass,company_img_url,company_tel,company_email,company_add,job_add,company_date,legal_person,company_scale,financing,register,company_slogan,company_type) values(#{company_name},#{company_number},#{company_pass},#{company_img_url},#{company_tel},#{company_email},#{company_add},#{job_add},#{company_date},#{legal_person},#{company_scale},#{financing},#{register},#{company_slogan},#{company_type})")
//    Integer insertCompany(Company company);

    @Insert("INSERT INTO `it`.`company` ( `company_name`, `company_number`, `company_pass`,`company_tel`, `company_email`, `company_slogan`, `company_type`) VALUES (#{company_name},#{company_number},#{company_pass},#{company_tel},#{company_email},#{company_slogan},#{company_type})")
    Integer insertCompany(Company company);

    @Select("select * from company where company_id=#{company_id}")
    Company SelectComByID(Integer company_id);


    //搜索job_add
    @Select("select * from company where job_add like '%${job_add}%' and company_state='审核通过'")
    List<Company> findCompanyByAdd(String job_add);

    //搜索company_type
    @Select("select * from company where company_type like '%${company_type}%' and company_state='审核通过'")
    List<Company> findCompanyByType(String company_type);

    //搜索company_scale
    @Select("select * from company where company_scale like '%${company_scale}%' and company_state='审核通过'")
    List<Company> findCompanyByScale(String company_scale);

    //企业认证；
    @Select("SELECT * from company where company_id=#{company_id}")
    Company SelectCompanyByID(Integer company_id);

    @Update("update company set company_id=#{company_id},company_name=#{company_name},company_number=#{company_number},company_pass=#{company_pass},company_img_url=#{company_img_url},company_tel=#{company_tel},company_email=#{company_email},company_add=#{company_add},job_add=#{job_add},company_date=#{company_date},legal_person=#{legal_person},company_scale=#{company_scale},financing=#{financing},register=#{register} where company_id=#{company_id}")
    Integer updateCompany(Company company);

    @Select("select distinct company_type from it.company;")
    List<Company> findComType();

    @Select("select distinct job_add from it.company;")
    List<Company> findComAdd();



    //前端页面间Aworkcompany传值到positionItem(试)
    @Select("SELECT company.*,position.* FROM company join position on company.company_id=position.company_id where company.company_id=#{company_id}")
    List<CompanyAndPositionView> SelectComByID2(Integer company_id);

    //可视化
    @Select("select date_format(create_date,'%Y-%m-%d') as create_date,sum(count(*)) over (order by date_format(create_date,'%Y-%m-%d')) as companysum from company group by date_format(create_date,'%Y-%m-%d')")
    List<Company> findAllCom();

    //今日新增公司
    @Select("select count(*) as Todaysum from company where date(create_date) = curdate();")
    Integer FindTodayCom();

    //本月新增公司
    @Select("select count(*) as Monthsum from company where date_format(create_date,'%Y-%m')=date_format(now(),'%Y-%m')")
    Integer FindMonthCom();

    //可视化 公司领域饼图
    @Select("select company_type,sum(count(*)) over (order by company_type) as companytype from company group by company_type;")
    List<CompanyType> SumComType();


    //可视化 公司投递热门前十
    @Select("select count(*) as count,company.company_name from it.company join it.post on post.company_id=company.company_id group by company.company_id order by count(*) desc limit 10;")
    List<Company> top10Com();

    //可视化地图
    @Select("select job_add,sum(count(*)) over (order by job_add) as companysum from company group by job_add")
    List<CompanyAdd> findAddCom();


}


